package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.entity.Beer;
import com.entity.Customer;
import com.entity.Member;
import com.entity.Order;

public class OrderDAO {

	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@210.125.212.171:1521:xe";
	String userid = "beer";
	String userpasswd = "test";

	public OrderDAO() {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public int insert(String o_No, String mId, String bNo, int quantity) {

		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "insert into b_order (o_No, mId, bNo, quantity, o_Date) values(?,?,?,?,sysdate)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, o_No);
			pstmt.setString(2, mId);
			pstmt.setString(3, bNo);
			pstmt.setInt(4, quantity);
			// pstmt.setString(5, o_Date);

			int n = pstmt.executeUpdate();
			result = n;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public ArrayList<Order> select() {
		// System.out.println();
		ArrayList<Order> list = new ArrayList<Order>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		Beer beer;
		Member member;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "SELECT  o_No, b_order.mId mid, b_order.bNo bno, quantity, o_Date,"
					+ " beer.NAME bname, beer.nation bnation, beer.type_yn btype, beer.price bprice, beer.stock bstock,"
					+ " MEMBER.passwd mpasswd, MEMBER.NAME mname, member.address maddress, MEMBER.phone mphone,"
					+ " MEMBER.email memail, MEMBER.type_yn mtype "
					+ "FROM b_order, beer, MEMBER"
					+ " where b_order.bno = beer.bno and b_order.mid = member.mid";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				String no = rs.getString("o_No");
				String mId = rs.getString("mid");
				String bNo = rs.getString("bno");
				int quantity = rs.getInt("quantity");
				String date = rs.getString("o_Date");
				String bName = rs.getString("bname");
				String bNation = rs.getString("bnation");
				String bType = rs.getString("btype");
				int bPrice = rs.getInt("bprice");
				int bStock = rs.getInt("bstock");
				String mPasswd = rs.getString("mpasswd");
				String mName = rs.getString("mname");
				String mAddress = rs.getString("maddress");
				String mPhone = rs.getString("mphone");
				String mEmail = rs.getString("memail");
				String mType = rs.getString("mtype");

				beer = new Beer(bNo, bName, bType, bNation, bPrice, bStock);
				member = new Customer(mId, mPasswd, mName, mAddress, mPhone,
						mEmail);
				Order order = new Order(no, quantity, date, beer, member);
				list.add(order);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public ArrayList<Order> selectMyOrderList(String id) {

		ArrayList<Order> list = new ArrayList<Order>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Beer beer;
		Member member;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "SELECT  o_No, b_order.mId mid, b_order.bNo bno, quantity, o_Date,"
					+ " beer.NAME bname, beer.nation bnation, beer.type_yn btype, beer.price bprice, beer.stock bstock,"
					+ " MEMBER.passwd mpasswd, MEMBER.NAME mname, member.address maddress, MEMBER.phone mphone,"
					+ " MEMBER.email memail, MEMBER.type_yn mtype "
					+ "FROM b_order, beer, MEMBER"
					+ " where b_order.bno = beer.bno and b_order.mid = member.mid and b_order.mId = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				String no = rs.getString("o_No");
				String mId = rs.getString("mid");
				String bNo = rs.getString("bno");
				int quantity = rs.getInt("quantity");
				String date = rs.getString("o_Date");
				String bName = rs.getString("bname");
				String bNation = rs.getString("bnation");
				String bType = rs.getString("btype");
				int bPrice = rs.getInt("bprice");
				int bStock = rs.getInt("bstock");
				String mPasswd = rs.getString("mpasswd");
				String mName = rs.getString("mname");
				String mAddress = rs.getString("maddress");
				String mPhone = rs.getString("mphone");
				String mEmail = rs.getString("memail");
				String mType = rs.getString("mtype");

				beer = new Beer(bNo, bName, bType, bNation, bPrice, bStock);
				member = new Customer(mId, mPasswd, mName, mAddress, mPhone,
						mEmail);
				Order order = new Order(no, quantity, date, beer, member);
				list.add(order);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public int getCount() {

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int n = -1;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "select count(*) from b_order";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				n = rs.getInt("count(*)");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return n;
	}
	
	public int delete(String o_No) {
		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "delete from b_order where o_No = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, o_No);

			int n = pstmt.executeUpdate();
			result = n;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

}
